package com.stable.model;

import com.stable.spider.ths.XLSData;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;

import java.io.InputStream;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class HSSFUtil {

    public static void rowColumn(HSSFWorkbook workbook, HSSFRow row, int column, String cellValue, short color) {
        HSSFCell rowcell = row.createCell(column);
        rowcell.setCellValue(cellValue);

        if (color == Font.COLOR_RED) {
            HSSFCellStyle cellStyle = RedColor(workbook);
            rowcell.setCellStyle(cellStyle);
        }
    }

    public static void rowColumnFormula(HSSFWorkbook workbook, HSSFRow row, int column, String cellFormula, short color, String format) {
        HSSFCell rowcell = row.createCell(column);
        rowcell.setCellFormula(cellFormula);

        if (color == Font.COLOR_RED && StringUtils.isNotBlank(format)) {
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(Font.COLOR_RED);
            cellStyle.setFont(font);
            cellStyle.setDataFormat(workbook.createDataFormat().getFormat(format));

            rowcell.setCellStyle(cellStyle);
        } else if (color == Font.COLOR_RED) {
            HSSFCellStyle cellStyle = RedColor(workbook);
            rowcell.setCellStyle(cellStyle);
        } else if (StringUtils.isNotBlank(format)) {
            HSSFCellStyle cellStyle = Format(workbook, format);
            rowcell.setCellStyle(cellStyle);
        }
    }


    public static void rowFormula(HSSFWorkbook workbook, HSSFSheet sheet, int row, String title, String listColum, List<XLSData> list, String format) {
        HSSFRow row2 = sheet.createRow(row);
        HSSFCell row2cell0 = row2.createCell(0);
        row2cell0.setCellValue(title);

        Map<Integer, String> map = new HashMap<>();
        map.put(1, "A");
        map.put(2, "B");
        map.put(3, "C");
        map.put(4, "D");
        map.put(5, "E");
        map.put(6, "F");
        map.put(7, "G");
        map.put(8, "H");
        map.put(9, "I");
        map.put(10, "J");
        map.put(11, "K");
        map.put(12, "L");

        for (int i = 0; i < 10; i++) {
            String key = map.get(i + 2);
            String nextKey = map.get(i + 3);
            //cs当前列，ct下一列
            String cs = listColum.replaceAll("CS", key).replaceAll("CT", nextKey);
            HSSFCell row4cell1 = row2.createCell(i + 1);
            row4cell1.setCellFormula(cs);

            if (StringUtils.isNotBlank(format)) {
                HSSFCellStyle cellStyle = Format(workbook, format);
                row4cell1.setCellStyle(cellStyle);
            }
            if (row4cell1.getNumericCellValue() < 0) {
                HSSFCellStyle cellStyle = RedColor(workbook);
                row4cell1.setCellStyle(cellStyle);
            }
        }

    }

    public static HSSFRow row(HSSFWorkbook workbook, HSSFSheet sheet, int row, String title, String listColum, List<XLSData> list) {
        HSSFRow row2 = sheet.createRow(row);
        HSSFCell row2cell0 = row2.createCell(0);
        row2cell0.setCellValue(title);

        list.stream().filter(xlsData -> StringUtils.equals(xlsData.getKey(), listColum)).forEach(xlsData -> {
            if (0 != xlsData.getYData1()) {
                HSSFCell row2cell1 = row2.createCell(1);
                row2cell1.setCellValue(new BigDecimal(xlsData.getYData1()).doubleValue());
                if (xlsData.getYData1() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell1.setCellStyle(cellStyle);
                }
            }

            if (0 != xlsData.getYData2()) {
                HSSFCell row2cell2 = row2.createCell(2);
                row2cell2.setCellValue(xlsData.getYData2());
                if (xlsData.getYData2() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell2.setCellStyle(cellStyle);
                }
            }

            if (0 != xlsData.getYData3()) {
                HSSFCell row2cell3 = row2.createCell(3);
                row2cell3.setCellValue(xlsData.getYData3());
                if (xlsData.getYData3() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell3.setCellStyle(cellStyle);
                }
            }

            if (0 != xlsData.getYData4()) {
                HSSFCell row2cell4 = row2.createCell(4);
                row2cell4.setCellValue(xlsData.getYData4());
                if (xlsData.getYData4() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell4.setCellStyle(cellStyle);
                }
            }

            if (xlsData.getYData5() != null && 0 != xlsData.getYData5()) {
                HSSFCell row2cell5 = row2.createCell(5);
                row2cell5.setCellValue(xlsData.getYData5());
                if (xlsData.getYData5() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell5.setCellStyle(cellStyle);
                }
            }

            if (xlsData.getYData6() != null && 0 != xlsData.getYData6()) {
                HSSFCell row2cell6 = row2.createCell(6);
                row2cell6.setCellValue(xlsData.getYData6());
                if (xlsData.getYData6() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell6.setCellStyle(cellStyle);
                }
            }

            if (xlsData.getYData7() != null && 0 != xlsData.getYData7()) {
                HSSFCell row2cell7 = row2.createCell(7);
                row2cell7.setCellValue(xlsData.getYData7());
                if (xlsData.getYData7() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell7.setCellStyle(cellStyle);
                }
            }

            if (xlsData.getYData8() != null && 0 != xlsData.getYData8()) {
                HSSFCell row2cell8 = row2.createCell(8);
                row2cell8.setCellValue(xlsData.getYData8());
                if (xlsData.getYData8() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell8.setCellStyle(cellStyle);
                }
            }

            if (xlsData.getYData9() != null && 0 != xlsData.getYData9()) {
                HSSFCell row2cell9 = row2.createCell(9);
                row2cell9.setCellValue(xlsData.getYData9());
                if (xlsData.getYData9() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell9.setCellStyle(cellStyle);
                }
            }

            if (xlsData.getYData10() != null && 0 != xlsData.getYData10()) {
                HSSFCell row2cell10 = row2.createCell(10);
                row2cell10.setCellValue(xlsData.getYData10());
                if (xlsData.getYData10() < 0) {
                    HSSFCellStyle cellStyle = RedColor(workbook);
                    row2cell10.setCellStyle(cellStyle);
                }
            }
        });
        return row2;
    }

    public static HSSFCellStyle Blog(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    public static HSSFCellStyle RedColor(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        cellStyle.setFont(font);
        return cellStyle;
    }

    public static HSSFCellStyle GreenColor(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setColor((short) 11);
        cellStyle.setFont(font);
        return cellStyle;
    }

    public static HSSFCellStyle Format(HSSFWorkbook workbook, String format) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(workbook.createDataFormat().getFormat(format));
        return cellStyle;
    }

    public static List<XLSData> xlsParse(List rowTitle, String urlStr) {
        List<XLSData> list = new ArrayList();
        try {
            URL url = new URL(urlStr);
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            // 设置超时间为3秒
            conn.setConnectTimeout(3 * 1000);
            // 防止屏蔽程序抓取而返回403错误
            conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");

            // 得到输入流
            InputStream inputStream = conn.getInputStream();

//            String path = "C:\\Users\\chou\\Downloads\\300104_benefit_report.xls";
//            FileInputStream is = new FileInputStream(path);
            HSSFWorkbook excel = new HSSFWorkbook(inputStream);
            //获取第一个sheet
            HSSFSheet sheet = excel.getSheetAt(0);
            int firstRowIndex = sheet.getFirstRowNum() + 1;   //第一行是列名，所以不读
            int lastRowIndex = sheet.getLastRowNum();
//            System.out.println("firstRowIndex: " + firstRowIndex);
//            System.out.println("lastRowIndex: " + lastRowIndex);

            for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
//                System.out.println("rIndex: " + rIndex);
                Row row = sheet.getRow(rIndex);
                if (row != null) {
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    boolean need = false;
                    XLSData xlsData = new XLSData();

                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                        Cell cell = row.getCell(cIndex);
                        if (cell != null) {
                            String cellContent = cell.toString();
                            //取第一列
                            if (rowTitle.contains(cellContent) && cIndex == 0) {
                                need = true;
                                xlsData.setKey(cell.toString());
                            }
                            //详细的年份对应的数据
                            else if (need) {
                                String text = cell.toString().replaceAll("%","");
                                if (cellContent.contains("E")) {
                                    text = new BigDecimal(cellContent + "").toString();
                                }
                                if (StringUtils.isBlank(text) || StringUtils.equals("--", text)) text = "0";
                                if (cIndex == 1) {
                                    xlsData.setYData1(Double.parseDouble(text));
                                } else if (cIndex == 2) {
                                    xlsData.setYData2(Double.parseDouble(text));
                                } else if (cIndex == 3) {
                                    xlsData.setYData3(Double.parseDouble(text));
                                } else if (cIndex == 4) {
                                    xlsData.setYData4(Double.parseDouble(text));
                                } else if (cIndex == 5) {
                                    xlsData.setYData5(Double.parseDouble(text));
                                } else if (cIndex == 6) {
                                    xlsData.setYData6(Double.parseDouble(text));
                                } else if (cIndex == 7) {
                                    xlsData.setYData7(Double.parseDouble(text));
                                } else if (cIndex == 8) {
                                    xlsData.setYData8(Double.parseDouble(text));
                                } else if (cIndex == 9) {
                                    xlsData.setYData9(Double.parseDouble(text));
                                } else if (cIndex == 10) {
                                    xlsData.setYData10(Double.parseDouble(text));
                                } else if (cIndex == 11) {
                                    xlsData.setYData11(Double.parseDouble(text));
                                } else if (cIndex == 12) {
                                    xlsData.setYData12(Double.parseDouble(text));
                                }

                                if (cIndex == lastCellIndex - 1) {
                                    list.add(xlsData);
                                }
                            } else {
                                continue;
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}
